﻿CREATE PROCEDURE [dbo].[spAdjustInventory] (
	@InventoryID int,
	@Qty int,
	@UnitCost money,
	@InventoryTransactionTypeCode char(1) )
AS
	begin try
		declare @TransactionCount int
		set @TransactionCount = @@TRANCOUNT
		begin transaction

		declare @CreditAccountID int
		declare @DebitAccountID int

		if exists( select 1 from tblProduct T1
				join tblInventory T2 on T1.ProductID = T2.ProductID
			where T2.InventoryID = @InventoryID
				and T1.TrackIndividual = 1)
			raiserror( 'Inventory adjustments are not allowed for individually tracked products.  Use Inventory Reconciliation instead', 16, 1 )

		if @Qty > 0
			select @CreditAccountID = T3.CostOfGoodsAdjustmentAccountID, @DebitAccountID = T3.InventoryAccountID
			from tblInventory T1
				join tblProduct T2 on T1.ProductID = T2.ProductID
				join tblOwner T3 on T2.OwnerID = T3.OwnerID
			where T1.InventoryID = @InventoryID
		else
			select @CreditAccountID = T3.InventoryAccountID, @DebitAccountID = T3.CostOfGoodsAdjustmentAccountID
			from tblInventory T1
				join tblProduct T2 on T1.ProductID = T2.ProductID
				join tblOwner T3 on T2.OwnerID = T3.OwnerID
			where T1.InventoryID = @InventoryID

		insert into tblInventoryTransaction ( InventoryTransactionTypeCode, 
			InventoryTransactionStatusCode, 
			Qty,
			InventoryID, 
			EffectiveDate,
			UnitCost,
			DebitAccountID,
			CreditAccountID )
		select @InventoryTransactionTypeCode,
			'C',
			@Qty,
			@InventoryID,
			GETDATE(),
			@UnitCost,
			@DebitAccountID,
			@CreditAccountID

		-- If the quantity is being adjusted up then apply backorders to the available inventory.
		if @Qty > 0
			while (select Qty from tblInventory where InventoryID = @InventoryID ) > 0
			begin
				declare @OrderID int
				set @OrderID = null
				select top 1 @OrderID = T1.OrderID
				from tblInventoryTransaction T1
					join tblInventory T2 on T1.InventoryID = T2.InventoryID
					join tblInventory T3 on T2.ProductID = T3.ProductID
				where T3.InventoryID = @InventoryID
					and T1.InventoryTransactionStatusCode = 'B'
				order by coalesce( T1.Priority, 0 ), T1.EffectiveDate

				if @OrderID is null 
					break

				exec spAllocateInventory @OrderID, 0
			end
		commit transaction
	end try
	begin catch
		if @TransactionCount = 1
			rollback transaction
		print error_message()
		exec usp_RethrowError
		return
	end catch

RETURN 0